package com.ivy.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

import com.ivy.dao.SchoolDao;
import com.ivy.dao.TeacherDao;
import com.ivy.entity.School;
import com.ivy.entity.Teacher;
import com.ivy.utils.DBConnection;
import com.ivy.utils.JdbcUtils;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.ResultSet;

public class SchoolDaoImpl implements SchoolDao {

	@Override
	public List<School> findAllSchool() {
		String sql = "select * from school;";
		List<School> schools = new ArrayList();
		
		Connection con = (Connection) DBConnection.getConnection();
		PreparedStatement pst=null;
		ResultSet rs=null;
		try {
			pst=(PreparedStatement) con.prepareStatement(sql);
			rs=(ResultSet) pst.executeQuery();
			while(rs.next()) {
				School school = new School();
				school.setId(rs.getInt("id"));
				school.setSname(rs.getString("sname"));
				school.setListpath(rs.getString("listpath"));
				school.setListcookie(rs.getString("listcookie"));
				school.setReferer(rs.getString("referer"));
				school.setCookiepath(rs.getString("cookiepath"));
				school.setCoursepath(rs.getString("coursepath"));
				schools.add(school);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}	
		DBConnection.closeAll(rs,pst, con);
		
		return schools;
	}

	@Override
	public School findSchoolById(int id) {
		String sql = "select * from school where id = "+id+";";
		School school = null;
		
		Connection con = (Connection) DBConnection.getConnection();
		PreparedStatement pst=null;
		ResultSet rs=null;
		try {
			pst=(PreparedStatement) con.prepareStatement(sql);
//			pst.setInt(1, id);
			rs=(ResultSet) pst.executeQuery();
			if(rs.next()) {
				school = new School();
				school.setId(rs.getInt("id"));
				school.setSname(rs.getString("sname"));
				school.setListpath(rs.getString("listpath"));
				school.setListcookie(rs.getString("listcookie"));
				school.setReferer(rs.getString("referer"));
				school.setCookiepath(rs.getString("cookiepath"));
				school.setCoursepath(rs.getString("coursepath"));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}	
		DBConnection.closeAll(rs,pst, con);
		return school;
	}

	
	public static void main(String[] args) {
		SchoolDaoImpl td=new SchoolDaoImpl();
		List<School> t= td.findAllSchool();
		for(School s : t){
			System.out.println(s.getSname());
			
		}
		
		School school = td.findSchoolById(1);
		System.out.println(school.getSname());
		
	}
}
